﻿/****************************************************
创建者: ljj
创建时间: 2018-01-23
修改:
修改时间: 
功能: 翻单量计算
******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.Cache;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using george.Helper;
using george.Web.Helper;
using InventoryToPredictAPI.model;
using NHibernateVertica;

namespace InventoryToPredictAPI
{
    public class GoodsReorderTotalDemandHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：ljj
        /// Create Date：2018-01-23
        [MustLogin]
        [Description("翻单量计算信息")]
        [ReturnType(typeof(PaperData<GoodsReorderTotalDemandModel.ListModel>))]
        public ApiResult GetGoodsReorderTotalDemandList([ParamDescription("大类")] string KindID = "",
            [ParamDescription("子项目")] string ItemOrSub = "", [ParamDescription("性别")] string SexID = "",
            [ParamDescription("畅滞销状态")] string AllCzpFlagNow = "", [ParamDescription("畅滞销状态")] string ERPFlag = "",
            [ParamDescription("开始时间")] DateTime StartDate = default(DateTime),
            [ParamDescription("结束时间")] DateTime EndDate = default(DateTime),
            [ParamDescription("货号")] string MaterialID = "", [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                if (string.IsNullOrEmpty(companyID))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(extrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var kindIDStr = KindID.GetString();
                var itemOrSubStr = ItemOrSub.GetString();
                var materialIDStr = MaterialID.GetString();
                var tempTableName = $"ntmp.ntmp_vw_Reorder_Total_Demand_Aggre_{extrSource}_{companyID}";
                if (!Helper.JudgeView("ntmp", tempTableName))
                {
                    CreateViewTempTable(tempTableName, extrSource, companyID);
                }
                var count = ReturnGoodsReorderTotalDemandCount(tempTableName, extrSource, companyID, kindIDStr,
                    itemOrSubStr, SexID, AllCzpFlagNow, ERPFlag, StartDate, EndDate, materialIDStr);
                var result = PaperData<GoodsReorderTotalDemandModel.ListModel>.Create(count, Size, Page);
                var list = ReturnGoodsReorderTotalDemandList(tempTableName, extrSource, companyID, kindIDStr,
                    itemOrSubStr, SexID, AllCzpFlagNow, ERPFlag, StartDate, EndDate, materialIDStr, Page, Size);
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败!");
            }
        }

        /// <summary>
        /// 翻单量计算信息
        /// </summary>
        /// <param name="TempTableName"></param>
        /// <param name="ExtrSource"></param>
        /// <param name="CompanyID"></param>
        /// <param name="KindIDStr"></param>
        /// <param name="ItemOrSubStr"></param>
        /// <param name="SexID"></param>
        /// <param name="AllCzpFlagNow"></param>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="MaterialIDStr"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-01-24
        private IList<GoodsReorderTotalDemandModel.ListModel> ReturnGoodsReorderTotalDemandList(string TempTableName,
            string ExtrSource, string CompanyID, string KindIDStr, string ItemOrSubStr, string SexID,
            string AllCzpFlagNow, string ERPFlag, DateTime StartDate, DateTime EndDate, string MaterialIDStr, int Page,
            int Size)
        {
            try
            {
                var sql = $@"SELECT  *
FROM    ( SELECT    a.* ,
                    ROW_NUMBER() OVER ( ORDER BY a.ExtrSource , a.CompanyID , a.ReorderDate DESC , kind.KindID, sex.SexID, a.ItemOrSub, a.StyleCode, a.MaterialID, a.SeasonName, a.ColorName ) AS Row
          FROM      {TempTableName} a
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Kind
                                 WHERE  ExtrSource = '{ExtrSource}'
                                        {(string.IsNullOrEmpty(KindIDStr) ? "" : $"AND KindID IN {KindIDStr}")}
                               ) kind ON a.ExtrSource = kind.ExtrSource
                                         AND a.KindName = kind.KindName
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Sex
                                 WHERE  ExtrSource = '{ExtrSource}'
                                        {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                               ) sex ON a.ExtrSource = sex.ExtrSource
                                        AND a.SexName = sex.SexName
          WHERE     a.ExtrSource = '{ExtrSource}'
                    AND a.CompanyID = '{CompanyID}'
                    {(string.IsNullOrEmpty(MaterialIDStr) ? "" : $"AND a.MaterialID IN {MaterialIDStr}")}
                    {(string.IsNullOrEmpty(KindIDStr) ? "" : $"AND kind.KindID IN {KindIDStr}")}
                    {(string.IsNullOrEmpty(SexID) ? "" : $"AND sex.SexID = '{SexID}'")}
                    {(string.IsNullOrEmpty(ItemOrSubStr) ? "" : $"AND a.ItemOrsub IN {ItemOrSubStr}")}
                    {(StartDate == default(DateTime) ? "" : $"AND a.ReorderDate >= '{StartDate:yyyy-MM-dd}'")}
                    {(EndDate == default(DateTime) ? "" : $"AND a.ReorderDate <= '{EndDate:yyyy-MM-dd}'")}
                    {(string.IsNullOrEmpty(AllCzpFlagNow) ? "" : $"AND a.AllCzpFlagNow = '{AllCzpFlagNow}'")}
                    {(string.IsNullOrEmpty(ERPFlag) ? "" : $"AND a.ERPFlag = {ERPFlag}")}
        ) table1
WHERE   Row BETWEEN ( {Page} - 1 ) * {Size} + 1 AND {Page} * {Size}";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<GoodsReorderTotalDemandModel.ListModel>.ConvertToModel(dt);
                return list;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 翻单量计算信息条数
        /// </summary>
        /// <param name="TempTableName"></param>
        /// <param name="ExtrSource"></param>
        /// <param name="CompanyID"></param>
        /// <param name="KindIDStr"></param>
        /// <param name="ItemOrSubStr"></param>
        /// <param name="SexID"></param>
        /// <param name="AllCzpFlagNow"></param>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="MaterialIDStr"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-01-24
        private int ReturnGoodsReorderTotalDemandCount(string TempTableName, string ExtrSource, string CompanyID,
            string KindIDStr, string ItemOrSubStr, string SexID, string AllCzpFlagNow, string ERPFlag,
            DateTime StartDate, DateTime EndDate, string MaterialIDStr)
        {
            try
            {
                var sql = $@"SELECT  Count(1)
FROM    ( SELECT    a.* ,
                    ROW_NUMBER() OVER ( ORDER BY a.ExtrSource , a.CompanyID , a.ReorderDate DESC , kind.KindID, sex.SexID, a.ItemOrSub, a.StyleCode, a.MaterialID, a.SeasonName, a.ColorName ) AS Row
          FROM      {TempTableName} a
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Kind
                                 WHERE  ExtrSource = '{ExtrSource}'
                                        {(string.IsNullOrEmpty(KindIDStr) ? "" : $"AND KindID IN {KindIDStr}")}
                               ) kind ON a.ExtrSource = kind.ExtrSource
                                         AND a.KindName = kind.KindName
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Sex
                                 WHERE  ExtrSource = '{ExtrSource}'
                                        {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                               ) sex ON a.ExtrSource = sex.ExtrSource
                                        AND a.SexName = sex.SexName
          WHERE     a.ExtrSource = '{ExtrSource}'
                    AND a.CompanyID = '{CompanyID}'
                    {(string.IsNullOrEmpty(MaterialIDStr) ? "" : $"AND a.MaterialID IN {MaterialIDStr}")}
                    {(string.IsNullOrEmpty(KindIDStr) ? "" : $"AND kind.KindID IN {KindIDStr}")}
                    {(string.IsNullOrEmpty(SexID) ? "" : $"AND sex.SexID = '{SexID}'")}
                    {(string.IsNullOrEmpty(ItemOrSubStr) ? "" : $"AND a.ItemOrsub IN {ItemOrSubStr}")}
                    {(StartDate == default(DateTime) ? "" : $"AND a.ReorderDate >= '{StartDate:yyyy-MM-dd}'")}
                    {(EndDate == default(DateTime) ? "" : $"AND a.ReorderDate <= '{EndDate:yyyy-MM-dd}'")}
                    {(string.IsNullOrEmpty(AllCzpFlagNow) ? "" : $"AND a.AllCzpFlagNow = '{AllCzpFlagNow}'")}
                    {(string.IsNullOrEmpty(ERPFlag) ? "" : $"AND a.ERPFlag = {ERPFlag}")}
        ) table1";
                var cmd = Helper.GetSqlStringCommand(sql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                return count;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        private void CreateViewTempTable(string TempTableName, string ExtrSource, string CompanyID)
        {
            try
            {
                var sql = $@"DROP VIEW IF EXISTS {TempTableName};
CREATE VIEW {TempTableName}
AS
    SELECT DISTINCT
            a.ExtrSource ,
            a.CompanyID ,
            a.MaterialID ,
            c.KindName ,
            c.SexName ,
            b.ItemOrsub ,
            c.StyleCode ,
            c.MaterialShortName ,
            c.SeasonName ,
            c.ColorName ,
            a.MoveDays ,
            a.AllCzpFlagNow ,
            c.MinPurQty AS DisplayQty,
            e.VendCustCode ,
            e.VendCustName ,
            f.FactFOB ,
            g.ReorderDate ,
            g.ProductDays ,
            g.JobDays ,
            g.ArriverDate ,
            g.SaleDays ,
            g.MonthSaleQty ,
            g.PlayReqQty ,
            g.TotalQty ,
            g.StockQty ,
            g.OrderQty ,
            CASE WHEN ISNULL(z.CompanyID, '') <> '' THEN 1
                 ELSE 0
            END AS ERPFlag ,
            (z.CheckDate::DATE)::VARCHAR ,
            CASE WHEN z.FromToday > 99 THEN '99+'
                 ELSE z.FromToday::VARCHAR
            END AS FromToday ,
            z.StartShipDate ,
            z.BillNo ,
            z.ErpOrderQty ,
            g.InitQty ,
            g.RowNumber
    FROM    ( SELECT DISTINCT
                        ExtrSource ,
                        CompanyID ,
                        MaterialID ,
                        MAX(SaleDays) AS MoveDays ,
                        MAX(AllCzpFlagNow) AS AllCzpFlagNow
              FROM      ndw.ndw_SkcSaleRateRow
              WHERE     ExtrSource = '{ExtrSource}'
                        AND CompanyID = '{CompanyID}'
                        AND SuppleDate::date = CURRENT_DATE
                        AND AllCzpFlagNow IN ( '爆', '畅', '平' )
                        AND SaleDays >= 14
              GROUP BY  ExtrSource ,
                        CompanyID ,
                        MaterialID
            ) a
            INNER JOIN ( SELECT *
                         FROM   public.vw_Mat_MaterialTOKind
                         WHERE  CompanyID = '{CompanyID}'
                       ) b ON a.CompanyID = b.CompanyID
                              AND a.MaterialID = b.MaterialID
            INNER JOIN ( SELECT *
                         FROM   nods.nods_erp_SD_Mat_Material2
                         WHERE  ExtrSource = '{ExtrSource}'
                                AND CompanyID = '{CompanyID}'
                       ) c ON a.ExtrSource = c.ExtrSource
                              AND a.CompanyID = c.CompanyID
                              AND a.MaterialID = c.MaterialID
            LEFT  JOIN ( SELECT *
                         FROM   nods.nods_erp_Bas_InterCompany
                         WHERE  ExtrSource = '{ExtrSource}'
                                AND CompanyID = '{CompanyID}'
                       ) e ON c.ExtrSource = e.ExtrSource
                              AND c.CompanyID = e.CompanyID
                              AND c.VendCustID = e.VendCustID
            LEFT  JOIN ( SELECT ExtrSource ,
                                CompanyID ,
                                MaterialID ,
                                MAX(FactFOB) AS FactFOB
                         FROM   nods.nods_erp_FICA_Bas_HisCostPrice
                         WHERE  CompanyID = '{CompanyID}'
                         GROUP BY ExtrSource ,
                                CompanyID ,
                                MaterialID
                       ) f ON a.CompanyID = f.CompanyID
                              AND a.MaterialID = f.MaterialID
            LEFT JOIN ( SELECT  * 
                        FROM    ngoods.ngoods_Reorder_Total_Demand_Aggre
                        WHERE   ExtrSource = '{ExtrSource}'
                                AND CompanyID = '{CompanyID}'
                      ) g ON a.CompanyID = g.CompanyID
                             AND a.MaterialID = g.MaterialID
            LEFT JOIN ( SELECT  a.CompanyID ,
                                a.CheckDate ,
                                b.StartShipDate,
                                b.BillNo,
                                CURRENT_DATE - a.CheckDate::DATE AS FromToday ,
                                b.MaterialID,
                                SUM(b.Qty) as ErpOrderQty
                        FROM    ( SELECT    *
                                  FROM      nods.nods_erp_SD_Pur_OrderMaster
                                  WHERE     ExtrSource = '{ExtrSource}'
                                            AND CompanyID = '{CompanyID}'
                                ) a
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Pur_OrderDetail
                                             WHERE  ExtrSource = '{ExtrSource}'
                                                    AND CompanyID = '{CompanyID}'
                                           ) b ON a.CompanyID = b.CompanyID
                                                  AND a.BillNo = b.BillNo
                        WHERE   a.ExtrSource = '{ExtrSource}'
                                AND a.CompanyID = '{CompanyID}'
                                AND a.billstatus = 4
                        GROUP BY a.ExtrSource ,
                                a.CompanyID ,
                                a.CheckDate ,b.StartShipDate,b.BillNo,
                                b.MaterialID
                      ) z ON g.CompanyID = z.CompanyID
                             AND g.MaterialID = z.MaterialID
                             AND ISNULL(g.ReorderDate, '') < ISNULL(z.CheckDate, '');";
                var cmd = Helper.GetSqlStringCommand(sql);
                Helper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：ljj
        /// Create Date：2018-01-24
        [MustLogin]
        [Description("翻单量计算辅助信息")]
        [ReturnType(typeof(IList<GoodsReorderTotalDemandModel.ItemModel>))]
        public ApiResult GetGoodsReorderTotalDemandItem([ParamDescription("货号")] string MaterialID)
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                if (string.IsNullOrEmpty(companyID))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(extrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@"SELECT  * ,
        CASE ISNULL(IsOld, 0)
          WHEN 0 THEN '新店'
          ELSE '老店'
        END AS ShopType
FROM    ngoods.ngoods_Reorder_Total_Demand
WHERE   ExtrSource = '{extrSource}'
        AND CompanyID = '{companyID}'
        AND MaterialID = '{MaterialID}'
ORDER BY IsOld ,
         InfoName ,
         month;";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<GoodsReorderTotalDemandModel.ItemModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败!");
            }
        }

        /// Writer：ljj
        /// Create Date：2018-01-24
        [MustLogin]
        [Description("计算翻单量")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult CalGoodsReorderTotalDemand([ParamDescription("货号")] string MaterialID)
        {
            try
            {
                if (string.IsNullOrEmpty(MaterialID)) return Error("请选择货号!");
                var dateTime = DateTime.Now.ToString("yyyy-MM-dd");
                var server = IpAddressHelper.WebConfig("python-server"); //"192.168.10.127";
                var username = IpAddressHelper.WebConfig("python-server-user"); //"bi";
                var port = Convert.ToInt32(IpAddressHelper.WebConfig("python-server-port")); //"22";
                var password = IpAddressHelper.WebConfig("python-server-pass"); //"Km_etl2017";
                var cmdJudge = $"python /home/bi/scripts/python/etl/ndw/SP_ngoods_Reorder_Monitor.py";
                var cmd = $"python /home/bi/scripts/python/etl/ndw/SP_ngoods_Reorder.py {dateTime} {MaterialID}";
                var resJudge = ConnLinux(server, username, port, password, cmdJudge);
                if (resJudge.Contains("1")) return Error("当前有用户正在计算!");
                var result = ConnLinux(server, username, port, password, cmd);
                return result.Contains("success") ? Success(0) : Error(result);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("计算失败!");
            }
        }

        /// <summary>
        /// 导出翻单量计算
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-03-12
        public DataTable ExportGoodsReorderTotalDemand(string Keys)
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                var tempTableName = $"ntmp.ntmp_vw_Reorder_Total_Demand_Aggre_{extrSource}_{companyID}";
                if (!Helper.JudgeView("ntmp", tempTableName))
                {
                    CreateViewTempTable(tempTableName, extrSource, companyID);
                }
                var sql = $@"SELECT    a.* 
          FROM      {tempTableName} a
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Kind
                                 WHERE  ExtrSource = '{extrSource}'
                               ) kind ON a.ExtrSource = kind.ExtrSource
                                         AND a.KindName = kind.KindName
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Sex
                                 WHERE  ExtrSource = '{extrSource}'
                               ) sex ON a.ExtrSource = sex.ExtrSource
                                        AND a.SexName = sex.SexName
          WHERE     a.ExtrSource = '{extrSource}'
                    AND a.CompanyID = '{companyID}'
                    {Keys}
          ORDER BY a.ExtrSource , a.CompanyID , a.ReorderDate DESC , kind.KindID, sex.SexID, a.ItemOrSub, a.StyleCode, a.MaterialID, a.SeasonName, a.ColorName";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                throw(ex);
            }
        }

        /// <summary>
        /// 导出翻单量计算辅助信息
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-03-12
        public DataTable ExportGoodsReorderTotalDemandItem(string Keys)
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                var tempTableName = $"ntmp.ntmp_vw_Reorder_Total_Demand_Aggre_{extrSource}_{companyID}";
                if (!Helper.JudgeView("ntmp", tempTableName))
                {
                    CreateViewTempTable(tempTableName, extrSource, companyID);
                }
                var sql = $@"SELECT  * ,
        CASE ISNULL(IsOld, 0)
          WHEN 0 THEN '新店'
          ELSE '老店'
        END AS ShopType
FROM    ngoods.ngoods_Reorder_Total_Demand
WHERE   ExtrSource = '{extrSource}'
        AND CompanyID = '{companyID}'
        AND MaterialID IN (SELECT    a.MaterialID 
          FROM      {tempTableName} a
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Kind
                                 WHERE  ExtrSource = '{extrSource}'
                               ) kind ON a.ExtrSource = kind.ExtrSource
                                         AND a.KindName = kind.KindName
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Sex
                                 WHERE  ExtrSource = '{extrSource}'
                               ) sex ON a.ExtrSource = sex.ExtrSource
                                        AND a.SexName = sex.SexName
          WHERE     a.ExtrSource = '{extrSource}'
                    AND a.CompanyID = '{companyID}'
                    {Keys})
ORDER BY MaterialID ,
         IsOld ,
         InfoName ,
         month;";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                throw(ex);
            }
        }
    }
}
